Don't Trash your Intermediate Results, Cache 'em

In data warehouse and data mart systems, queries often take a long time to execute due to their complex nature. Query response times can be greatly improved by caching final/intermediate results of previous queries, and using them to answer later queries. An automatic caching system that makes intelligent decisions on what results to cache would be an important step towards knobs-free operation of a database system, essentially a step towards a database system that auto-tunes itself. In this paper we describe an automatic query caching system called Exchequer which is closely coupled with the optimizer to ensure that the caching system and the optimizer make mutually consistent decisions. In contrast, in existing work, such a close coupling is absent. We present a cache management/replacement algorithm which we call Incremental. Furthermore, existing approaches are either restricted to cube (slice/point) queries, or to caching just the final query results. On the other hand, our work is extensible and in fact presents a data-model independent framework and algorithm. Our experimental results attest to the efficacy of our cache management techniques and show that over a wide range of parameters (a) Exchequer’s query response times are lower by more than 30% compared to the best performing competitor, and (b) Exchequer can deliver the same response time as its competitor with just one tenth of the cache size.